前言:

複習一下 BigQuery 中三種監控方式:

• cloud monitoring
• information schema
• audit log (稽核日誌)

我們今天要使用的是第三種audit log的方式，我們要將 audit log 中紀錄 BigQuery 的資料匯出並且記錄到 BigQuery，再用 BigQuery 本身來分析它自己。

Step 1: 在 BigQuery 中建立資料集 bq_logs

在專案底下建立資料集:

先做一個查詢，等等可以在 cloud logging 中看到紀錄:

select current_date

Step 2: 到 Cloud logging 頁面，建立連接器

到 Cloud logging 頁面，點擊 BigQuery:

可以看到自動帶出的語法:

點擊 jobs.complete:

可以看到自動帶出的語法:

點擊更多動作，建立 sink:

注意要先做篩選，以免匯入不需要的資料，超出預期，這裡系統自動帶入剛剛做的篩選:

建立成功後可以看到如下:

進行幾個查詢以便後續分析使用。

SELECT * FROM bigquery-public-data.austin_bikeshare.bikeshare_stations

SELECT * FROM bigquery-public-data.austin_bikeshare.bikeshare_trips

Step 3: 在 資料集 bq_logs 底下創建 VIEW 表:

CREATE OR REPLACE VIEW
  bq_logs.v_querylogs AS
SELECT
  resource.labels.project_id,
  protopayload_auditlog.authenticationInfo.principalEmail,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.statementType,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.message,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.endTime,
  TIMESTAMP_DIFF(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.endTime,           protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime, MILLISECOND)/1000 AS run_seconds,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalProcessedBytes,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalSlotMs,
  ARRAY(SELECT as STRUCT datasetid, tableId FROM UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables)) as tables_ref,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalTablesProcessed,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.queryOutputRowCount,
  severity
FROM
  `ithome-bq-test.bq_logs.cloudaudit_googleapis_com_data_access`
ORDER BY
  startTime

即可看到在 bq_logs 底下多了 cloudaudit_googleapis_com_data_access 這個table:

對 cloudaudit_googleapis_com_data_access 進行查詢:

SELECT * FROM bq_logs.v_querylogs

即可看到剛剛做的動作。

這裡留意，我們無法看到在建立 sink 之前的資料，這是因為先前在 BigQuery 中執行其他查詢的時候，我們尚未建立 sink，而sink 也不具備回填的功能。

到這裡，就完成了將 cloud logging 匯出到 BigQuery了，一樣可以用 Data studio 進行視覺化的分析。

補充:

GCP 有提供關於BigQuery 效能分析的模板，可以參考 BigQuery System Tables Reports。

Summary:

Step 1: 在 BigQuery 中建立資料集 bq_logs
Step 2: 到 Cloud logging 頁面，建立連接器 (注意要先做篩選，以免匯入不需要的資料，超出預期)
Step 3: 在資料集 bq_logs 底下創建 VIEW 表 (這裡留意 sink 不具備回填的功能)

Reference:

Using BigQuery and Cloud Logging to Analyze BigQuery Usage
Exporting BigQuery usage logs to… BigQuery